package com.hujie.sqlqueryes.service.sql;

import com.hujie.sqlqueryes.dto.DslResultDTO;
import com.hujie.sqlqueryes.service.util.JsonEscapeUtil;
import com.hujie.sqlqueryes.service.util.SqlExpressionUtil;
import net.sf.jsqlparser.expression.DoubleValue;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.insert.Insert;
import net.sf.jsqlparser.statement.select.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

/**
 * INSERT语句处理器
 */
@Component
public class InsertStatementHandler implements SqlStatementHandler {

    private static final Logger logger = LoggerFactory.getLogger(InsertStatementHandler.class);

    @Override
    public boolean supports(Statement statement) {
        return statement instanceof Insert;
    }

    @Override
    public DslResultDTO handle(Statement statement) {
        try {
            Insert insertStatement = (Insert) statement;
            String dsl = buildDslFromInsert(insertStatement);
            logger.info("INSERT语句转换完成");
            logger.debug("生成的DSL: {}", dsl);
            return DslResultDTO.success(dsl);
        } catch (Exception e) {
            logger.error("INSERT语句转换失败: {}", e.getMessage(), e);
            return DslResultDTO.error("INSERT语句转换失败: " + e.getMessage());
        }
    }

    private String buildDslFromInsert(Insert insertStatement) {
        Table table = insertStatement.getTable();
        String tableName = processTableName(table.getName());
        List<Column> columns = insertStatement.getColumns();
        Object itemsList = getItemsList(insertStatement);

        // 统一处理所有值列表（无论来自VALUES还是SELECT）
        if (itemsList != null) {
            return buildDslFromItemsList(itemsList, columns, tableName);
        }

        // 未提取到任何值列表的异常情况
        return "{\n  \"error\": \"无法提取插入值列表\"\n}";
    }

    /**
     * 处理表名，去除方括号
     */
    private String processTableName(String tableName) {
        if (tableName != null && tableName.startsWith("[") && tableName.endsWith("]")) {
            return tableName.substring(1, tableName.length() - 1);
        }
        return tableName;
    }

    /**
     * 获取INSERT语句中的值列表（统一处理VALUES和SELECT子句）
     */
    private Object getItemsList(Insert insertStatement) {
        // 优先处理INSERT ... SELECT
        Statement selectStatement = insertStatement.getSelect();
        if (selectStatement instanceof Select) {
            logger.debug("检测到INSERT ... SELECT语法，提取SELECT子句的值");
            return extractValuesFromSelect((Select) selectStatement);
        }

        // 处理INSERT ... VALUES
        logger.debug("处理INSERT ... VALUES语法，提取VALUES子句的值");
        return extractValuesFromValues(insertStatement);
    }

    /**
     * 从INSERT ... SELECT中提取值（返回List<List<SelectItem<?>>>）
     */
    private Object extractValuesFromSelect(Select select) {
        Object selectBody = select.getSelectBody();

        // 简单SELECT（如SELECT 'John', 25）
        if (selectBody instanceof PlainSelect) {
            PlainSelect plainSelect = (PlainSelect) selectBody;
            List<SelectItem<?>> selectItems = plainSelect.getSelectItems();
            if (selectItems != null && !selectItems.isEmpty()) {
                List<List<SelectItem<?>>> singleRow = new ArrayList<>();
                singleRow.add(selectItems);
                logger.debug("从简单SELECT提取1行数据，{}个字段", selectItems.size());
                return singleRow;
            }
        }
        // UNION/UNION ALL（如SELECT a UNION SELECT b）
        else if (selectBody.getClass().getSimpleName().equals("SetOperationList")) {
            try {
                Method getSelectsMethod = selectBody.getClass().getMethod("getSelects");
                @SuppressWarnings("unchecked")
                List<Object> subQueries = (List<Object>) getSelectsMethod.invoke(selectBody);

                List<List<SelectItem<?>>> multiRowItems = new ArrayList<>();
                for (Object subQuery : subQueries) {
                    if (subQuery instanceof PlainSelect) {
                        List<SelectItem<?>> subItems = ((PlainSelect) subQuery).getSelectItems();
                        if (subItems != null && !subItems.isEmpty()) {
                            multiRowItems.add(subItems);
                        }
                    }
                }
                logger.debug("从UNION提取{}行数据", multiRowItems.size());
                return multiRowItems;
            } catch (Exception e) {
                logger.error("处理UNION失败: {}", e.getMessage());
            }
        }

        logger.warn("SELECT子句无法提取值");
        return null;
    }

    /**
     * 从INSERT ... VALUES中提取值（返回ExpressionList或MultiExpressionList）
     */
    private Object extractValuesFromValues(Insert insertStatement) {
        Object itemsList = null;

        // 尝试通过方法获取VALUES列表
        String[] methodNames = {"getItemsList", "getValuesList", "getExpressions"};
        for (String methodName : methodNames) {
            try {
                Method method = insertStatement.getClass().getMethod(methodName);
                itemsList = method.invoke(insertStatement);
                if (itemsList != null) {
                    logger.debug("通过{}方法获取VALUES列表", methodName);
                    return itemsList;
                }
            } catch (NoSuchMethodException e) {
                logger.debug("未找到{}方法（忽略）", methodName);
            } catch (Exception e) {
                logger.warn("调用{}方法失败: {}", methodName, e.getMessage());
            }
        }

        // 尝试通过字段获取VALUES列表
        try {
            Field[] fields = insertStatement.getClass().getDeclaredFields();
            for (Field field : fields) {
                if (field.getType().getSimpleName().endsWith("List")) {
                    field.setAccessible(true);
                    Object value = field.get(insertStatement);
                    if (value != null) {
                        logger.debug("通过字段{}获取VALUES列表", field.getName());
                        return value;
                    }
                }
            }
        } catch (Exception e) {
            logger.warn("通过字段获取VALUES列表失败: {}", e.getMessage());
        }

        logger.warn("VALUES子句无法提取值");
        return null;
    }

    /**
     * 统一处理值列表生成DSL（支持VALUES的Expression列表和SELECT的SelectItem列表）
     */
    private String buildDslFromItemsList(Object itemsList, List<Column> columns, String tableName) {
        StringBuilder dsl = new StringBuilder();
        dsl.append("{\n");
        dsl.append("  \"operation\": \"insert\",\n");
        dsl.append("  \"index\": \"").append(tableName).append("\",\n");

        try {
            // 处理来自SELECT的SelectItem列表（List<List<SelectItem<?>>>）
            if (itemsList instanceof List && !((List<?>) itemsList).isEmpty()) {
                Object firstItem = ((List<?>) itemsList).get(0);
                if (firstItem instanceof List && !((List<?>) firstItem).isEmpty()) {
                    Object firstSubItem = ((List<?>) firstItem).get(0);
                    if (firstSubItem instanceof SelectItem<?>) {
                        buildDslForSelectItemRows(dsl, (List<List<SelectItem<?>>>) itemsList, columns);
                        dsl.append("}");
                        return dsl.toString();
                    }
                }
            }

            // 处理来自VALUES的Expression列表
            String itemsListClassName = itemsList.getClass().getSimpleName();
            logger.debug("处理VALUES类型: {}", itemsListClassName);

            if ("MultiExpressionList".equals(itemsListClassName)) {
                buildDslForMultiExpressionList(dsl, itemsList, columns);
            } else if ("ExpressionList".equals(itemsListClassName)) {
                buildDslForExpressionList(dsl, itemsList, columns);
            } else {
                buildDslForGenericExpressionList(dsl, itemsList, columns);
            }
        } catch (Exception e) {
            logger.warn("处理值列表出错: {}", e.getMessage(), e);
            dsl.append("  \"doc\": {}\n");
        }

        dsl.append("}");
        return dsl.toString();
    }

    /**
     * 处理来自SELECT的多行SelectItem列表（生成docs数组）
     */
    private void buildDslForSelectItemRows(StringBuilder dsl, List<List<SelectItem<?>>> rows, List<Column> columns) {
        dsl.append("  \"docs\": [\n");
        List<String> docStrings = new ArrayList<>();

        for (List<SelectItem<?>> row : rows) {
            List<String> docFields = new ArrayList<>();
            for (int i = 0; i < columns.size() && i < row.size(); i++) {
                String columnName = columns.get(i).getColumnName();
                String value = getValueFromExpressionItem(row.get(i));
                logger.debug("处理SELECT字段: {} = {}", columnName, value);

                // 字符串转义处理
                if (!SqlExpressionUtil.isNumeric(value)) {
                    docFields.add("\"" + columnName + "\": \"" + JsonEscapeUtil.escapeJsonString(value) + "\"");
                } else {
                    docFields.add("\"" + columnName + "\": " + value);
                }
            }
            docStrings.add("    {" + String.join(", ", docFields) + "}");
        }

        dsl.append(String.join(",\n", docStrings));
        dsl.append("\n  ]\n");
    }

    /**
     * 处理MultiExpressionList类型（多行VALUES）
     */
    private void buildDslForMultiExpressionList(StringBuilder dsl, Object itemsList, List<Column> columns) {
        try {
            Method getExpressionListsMethod = itemsList.getClass().getMethod("getExpressionLists");
            @SuppressWarnings("unchecked")
            List<Object> expressionLists = (List<Object>) getExpressionListsMethod.invoke(itemsList);

            dsl.append("  \"docs\": [\n");
            List<String> docStrings = new ArrayList<>();

            for (Object expressionList : expressionLists) {
                Method getExpressionsMethod = expressionList.getClass().getMethod("getExpressions");
                @SuppressWarnings("unchecked")
                List<Expression> expressions = (List<Expression>) getExpressionsMethod.invoke(expressionList);

                List<String> docFields = new ArrayList<>();
                for (int i = 0; i < columns.size() && i < expressions.size(); i++) {
                    String columnName = columns.get(i).getColumnName();
                    String value = getValueFromExpression(expressions.get(i)); // 统一使用Expression提取方法
                    docFields.add(buildFieldString(columnName, value));
                }
                docStrings.add("    {" + String.join(", ", docFields) + "}");
            }

            dsl.append(String.join(",\n", docStrings));
            dsl.append("\n  ]\n");
        } catch (Exception e) {
            logger.warn("处理MultiExpressionList失败: {}", e.getMessage());
            dsl.append("  \"docs\": []\n");
        }
    }

    /**
     * 处理ExpressionList类型（单行VALUES）
     */
    private void buildDslForExpressionList(StringBuilder dsl, Object itemsList, List<Column> columns) {
        try {
            Method getExpressionsMethod = itemsList.getClass().getMethod("getExpressions");
            @SuppressWarnings("unchecked")
            List<Expression> expressions = (List<Expression>) getExpressionsMethod.invoke(itemsList);

            dsl.append("  \"doc\": {");
            List<String> docFields = new ArrayList<>();
            for (int i = 0; i < columns.size() && i < expressions.size(); i++) {
                String columnName = columns.get(i).getColumnName();
                String value = getValueFromExpression(expressions.get(i)); // 统一使用Expression提取方法
                docFields.add(buildFieldString(columnName, value));
            }
            dsl.append(String.join(", ", docFields));
            dsl.append("}\n");
        } catch (Exception e) {
            logger.warn("处理ExpressionList失败: {}", e.getMessage());
            dsl.append("  \"doc\": {}\n");
        }
    }

    /**
     * 处理通用类型的Expression列表
     */
    private void buildDslForGenericExpressionList(StringBuilder dsl, Object itemsList, List<Column> columns) {
        try {
            if (itemsList instanceof List) {
                List<?> items = (List<?>) itemsList;
                if (!items.isEmpty() && items.get(0).getClass().getSimpleName().equals("ExpressionList")) {
                    // 多行ExpressionList
                    dsl.append("  \"docs\": [\n");
                    List<String> docStrings = new ArrayList<>();
                    for (Object item : items) {
                        Method getExpressionsMethod = item.getClass().getMethod("getExpressions");
                        @SuppressWarnings("unchecked")
                        List<Expression> expressions = (List<Expression>) getExpressionsMethod.invoke(item);

                        List<String> docFields = new ArrayList<>();
                        for (int i = 0; i < columns.size() && i < expressions.size(); i++) {
                            String columnName = columns.get(i).getColumnName();
                            String value = getValueFromExpression(expressions.get(i));
                            docFields.add(buildFieldString(columnName, value));
                        }
                        docStrings.add("    {" + String.join(", ", docFields) + "}");
                    }
                    dsl.append(String.join(",\n", docStrings));
                    dsl.append("\n  ]\n");
                } else if (!items.isEmpty() && items.get(0) instanceof Expression) {
                    // 单行Expression列表
                    @SuppressWarnings("unchecked")
                    List<Expression> expressions = (List<Expression>) items;
                    dsl.append("  \"doc\": {");
                    List<String> docFields = new ArrayList<>();
                    for (int i = 0; i < columns.size() && i < expressions.size(); i++) {
                        String columnName = columns.get(i).getColumnName();
                        String value = getValueFromExpression(expressions.get(i));
                        docFields.add(buildFieldString(columnName, value));
                    }
                    dsl.append(String.join(", ", docFields));
                    dsl.append("}\n");
                }
            } else {
                // 单个ExpressionList
                Method getExpressionsMethod = itemsList.getClass().getMethod("getExpressions");
                @SuppressWarnings("unchecked")
                List<Expression> expressions = (List<Expression>) getExpressionsMethod.invoke(itemsList);
                dsl.append("  \"doc\": {");
                List<String> docFields = new ArrayList<>();
                for (int i = 0; i < columns.size() && i < expressions.size(); i++) {
                    String columnName = columns.get(i).getColumnName();
                    String value = getValueFromExpression(expressions.get(i));
                    docFields.add(buildFieldString(columnName, value));
                }
                dsl.append(String.join(", ", docFields));
                dsl.append("}\n");
            }
        } catch (Exception e) {
            logger.warn("处理通用Expression列表失败: {}", e.getMessage());
            dsl.append("  \"doc\": {}\n");
        }
    }

    /**
     * 从SelectItem中提取值（用于SELECT子句）
     */
    private String getValueFromExpressionItem(SelectItem<?> item) {
        try {
            String itemStr = item.toString().trim();
            logger.debug("处理SelectItem: [{}], 类型: [{}]", itemStr, item.getClass().getSimpleName());

            // 直接解析字符串常量（如'Test'）
            if (itemStr.startsWith("'") && itemStr.endsWith("'") && itemStr.length() > 2) {
                String value = itemStr.substring(1, itemStr.length() - 1);
                logger.debug("提取字符串常量: [{}] -> [{}]", itemStr, value);
                return value;
            }

            // 直接解析数字常量（如25）
            if (SqlExpressionUtil.isNumeric(itemStr)) {
                logger.debug("提取数字常量: [{}] -> [{}]", itemStr, itemStr);
                return itemStr;
            }

            // 从Expression中提取值
            Expression expression = null;
            try {
                Method getExpressionMethod = item.getClass().getMethod("getExpression");
                expression = (Expression) getExpressionMethod.invoke(item);
            } catch (Exception e) {
                logger.debug("获取Expression失败: {}", e.getMessage());
            }

            if (expression != null) {
                return getValueFromExpression(expression); // 复用Expression提取逻辑
            }

            // 处理AS语法（如'Test' AS name）
            if (itemStr.contains(" AS ")) {
                String[] parts = itemStr.split(" AS ", 2);
                String exprPart = parts[0].trim();
                if (exprPart.startsWith("'") && exprPart.endsWith("'") && exprPart.length() > 2) {
                    return exprPart.substring(1, exprPart.length() - 1);
                }
                if (SqlExpressionUtil.isNumeric(exprPart)) {
                    return exprPart;
                }
                return exprPart;
            }

            logger.warn("无法解析SelectItem，返回空值: [{}]", itemStr);
            return "";
        } catch (Exception e) {
            logger.error("提取SelectItem值异常: {}", e.getMessage());
            return "";
        }
    }

    /**
     * 从Expression中提取值（用于VALUES子句）
     */
    private String getValueFromExpression(Expression expression) {
        try {
            // 字符串常量（如StringValue('John')）
            if (expression instanceof StringValue) {
                String value = ((StringValue) expression).getValue();
                logger.debug("从StringValue提取: [{}] -> [{}]", expression, value);
                return value;
            }
            // 长整型（如LongValue(25)）
            if (expression instanceof LongValue) {
                String value = ((LongValue) expression).getStringValue();
                logger.debug("从LongValue提取: [{}] -> [{}]", expression, value);
                return value;
            }
            // 浮点型（如DoubleValue(3.14)）
            if (expression instanceof DoubleValue) {
                String value = ((DoubleValue) expression).toString();
                logger.debug("从DoubleValue提取: [{}] -> [{}]", expression, value);
                return value;
            }
            // 解析表达式字符串（如'test'或123）
            String exprStr = expression.toString().trim();
            if (exprStr.startsWith("'") && exprStr.endsWith("'") && exprStr.length() > 2) {
                return exprStr.substring(1, exprStr.length() - 1);
            }
            if (SqlExpressionUtil.isNumeric(exprStr)) {
                return exprStr;
            }
            return exprStr;
        } catch (Exception e) {
            logger.error("提取Expression值异常: {}", e.getMessage());
            return "";
        }
    }

    /**
     * 构建字段的JSON字符串（处理转义和数字类型）
     */
    private String buildFieldString(String columnName, String value) {
        if (!SqlExpressionUtil.isNumeric(value)) {
            return "\"" + columnName + "\": \"" + JsonEscapeUtil.escapeJsonString(value) + "\"";
        } else {
            return "\"" + columnName + "\": " + value;
        }
    }
}